const pool = require("../connection/sqlPool");

async function GETRECITE(username){
    const promisePool = pool.promise();
    try{
        const [rows_ru,fildes] = await promisePool.query('SELECT `recite_every` FROM `user_message` WHERE `username`=?',[username]);
        const [rows_re,fildes_re] = await promisePool.query('SELECT `word_id` FROM `Word_recite` WHERE `username` = ? AND `phase`<100',[username]);
        const recite_num = rows_re.length;
        return {tips:'获取成功',recite_num:recite_num,today_num:rows_ru[0].recite_every,rows_re}
    }catch(error){
        return {tips:'获取失败',msg:error.message}
    }
}

async function GETNEWWORDS(username,limits){
    const promisePool = pool.promise();
    try{
        const [rows,fileds] = await promisePool.query(
            'SELECT `Word`.`id` AS word_id FROM `Word` WHERE `Word`.`id` NOT IN(SELECT `Word_recite`.`word_id` FROM `Word_recite` WHERE `username`=?) ORDER BY RAND() LIMIT ?',[username,limits]
        );
        return rows;

    }catch(error){
        return {tips:'失败',msg:error.message};
    }
}

async function ADDTABLE(username,dataInsert){
    const promisePool = pool.promise();
    const table_name = 'today_'+username;
    const query = `CREATE TABLE ${table_name} (id INT AUTO_INCREMENT PRIMARY KEY,word_id INT,phease_today INT DEFAULT 40)`
    const queryInsert = 'INSERT INTO '+table_name+ '(`word_id`) VALUES ?'
    const data = JSON.parse(dataInsert);
    try{
        const [rows,fildes] = await promisePool.query(query);
        const [rows_i,fildes_i] = await promisePool.query(queryInsert,[data.map(item=>[item.word_id])]);
        return {rows_i};
    }catch(error){
        return {tips:'失败',msg:error.message};
    }
}

async function GETWORD(username){
    const promisePool = pool.promise();
    const table_name = 'today_'+username;
    const qury = 'SELECT * FROM `Word` WHERE `Word`.`id` IN (SELECT `word_id` FROM `'+ table_name +'`)'
    try{
        const [rows,fildes] = await promisePool.query(qury);
        return {rows}
    }catch(error){
        return {tips:'失败',msg:error.message}
    }
}

async function CLICK(username,id,is){
    const promisePool = pool.promise();
    const table_name = 'today_'+username;
    try{
        if(is==1){
            const qury_u = 'UPDATE `'+table_name+'` SET `phease_today` = `phease_today` + 20  WHERE `word_id` = ?'
            const qury_sp = 'SELECT `phease_today` FROM`'+table_name+'` WHERE `word_id` = ? '            
            const [rows_r,fildes_r] = await promisePool.query(qury_u,[id]);
            const [rows_sp,fildes_sp] = await promisePool.query(qury_sp,[id]);            

            if(rows_sp[0].phease_today>50){
                //当查询到某个单词今日已熟练
                //获取当前时间
                const currentTime = new Date();
                const formattedTime = currentTime.toISOString().slice(0, 19).replace('T', ' ');
                const [rows_s,fildes_s] = await promisePool.query(
                    'INSERT INTO `Word_recite` (`word_id`,`phase`,`username`,`recite_last`) VALUES(?,20,?,?) ON DUPLICATE KEY UPDATE `phase`=`phase`+20,`recite_last`=?',
                    [id,username,formattedTime,formattedTime]
                )
                const qury_d = 'DELETE FROM `'+ table_name + '`WHERE `phease_today` > 50;'
                const [rows_d,fildes_d] = await promisePool.query(qury_d);

                return {tips:'更改成功',rows_sp,rows_r,rows_s,rows_d}
            }else{
                return {tips:'continue',phease_today:rows_sp[0].phease_today}
            }
        }else{
            const qury_sp = 'SELECT `phease_today` FROM`'+table_name+'` WHERE `word_id` = ? '            
            const [rows_sp,fildes_sp] = await promisePool.query(qury_sp,[id]);
            if(rows_sp[0].phease_today<20){
                const qury_u = 'UPDATE `'+table_name+'` SET `phease_today` = 0 WHERE `word_id` = ?'
                const [rows_r,fildes_r] = await promisePool.query(qury_u,[id]);
                return {tips:'更改成功',rows_sp,rows_r}
            }else{
                const qury_u = 'UPDATE `'+table_name+'` SET `phease_today` = `phease_today` - 20  WHERE `word_id` = ?'
                const [rows_r,fildes_r] = await promisePool.query(qury_u,[id]);
                return {tips:'更改成功',rows_sp,rows_r}
            }                   
        }
    }catch(error){
        return {tips:'更改未成功',msg:error.message}
    }
}

async function COMPLETE(username){
    const promisePool = pool.promise();
    const table_name = 'today_'+username;
    try{
        const qury = 'DROP TABLE `'+table_name+'`'
        const [rows,fildes] = await promisePool.query(qury);
        return {tips:"删除成功",msg:"已完成今日学习",rows};
    }catch(error){
        return {tips:'删除失败',msg:error.message}
    }
    
}

async function GETTODAYLEN(username){
    const promisePool = pool.promise();
    const table_name = 'today_'+username;
    try{
        const qury = 'SELECT * FROM `'+table_name+'`';
        const [rows,fildes] = await promisePool.query(qury);
        return {tips:'获取成功',msg:rows.length};
    }catch(error){
        return {tips:'获取失败',msg:error.message}
    }
}


module.exports={GETRECITE,GETNEWWORDS,ADDTABLE,GETWORD,CLICK,COMPLETE,GETTODAYLEN};